﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class OpenReservationService
    {
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public static int Add(Model.OpenReservation model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into OpenReservation(");
            strSql.Append("OpenLabID,userid,used,date,lesson,confirmed)");
            strSql.Append(" values (");
            strSql.Append("@OpenLabID,@userid,@used,@date,@lesson,@confirmed)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@OpenLabID", SqlDbType.Int,4),
					new SqlParameter("@userid", SqlDbType.Int,4),
					new SqlParameter("@used", SqlDbType.VarChar,200),
					new SqlParameter("@date", SqlDbType.DateTime),
					new SqlParameter("@lesson", SqlDbType.Int,4),
					new SqlParameter("@confirmed", SqlDbType.Bit,1)};
            parameters[0].Value = model.OpenLabID;
            parameters[1].Value = model.userid;
            parameters[2].Value = model.used;
            parameters[3].Value = model.date;
            parameters[4].Value = model.lesson;
            parameters[5].Value = model.confirmed;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        public static Model.OpenReservation SelectByID(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,OpenLabID,userid,used,date,lesson,confirmed ");
            strSql.Append(" FROM OpenReservation ");
            strSql.Append(" where id = " + id);
            SqlDataReader dr = DbHelperSQL.ExecuteReader(strSql.ToString());
            Model.OpenReservation m = new Model.OpenReservation();
            dr.Read();
            m.id = int.Parse(dr[0].ToString());
            m.OpenLabID = int.Parse(dr[1].ToString());
            m.userid = int.Parse(dr[2].ToString());
            m.used = dr[3].ToString();
            m.date = Convert.ToDateTime(dr[4]);
            m.lesson = int.Parse(dr[5].ToString());
            m.confirmed = Convert.ToBoolean(dr[6]);
            dr.Close();
            return m;

        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public static bool Update(Model.OpenReservation model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update OpenReservation set ");
            strSql.Append("OpenLabID=@OpenLabID,");
            strSql.Append("userid=@userid,");
            strSql.Append("used=@used,");
            strSql.Append("date=@date,");
            strSql.Append("lesson=@lesson,");
            strSql.Append("confirmed=@confirmed");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@OpenLabID", SqlDbType.Int,4),
					new SqlParameter("@userid", SqlDbType.Int,4),
					new SqlParameter("@used", SqlDbType.VarChar,200),
					new SqlParameter("@date", SqlDbType.DateTime),
					new SqlParameter("@lesson", SqlDbType.Int,4),
					new SqlParameter("@confirmed", SqlDbType.Bit,1),
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = model.OpenLabID;
            parameters[1].Value = model.userid;
            parameters[2].Value = model.used;
            parameters[3].Value = model.date;
            parameters[4].Value = model.lesson;
            parameters[5].Value = model.confirmed;
            parameters[6].Value = model.id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public static void Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from OpenReservation ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static List<Model.OpenReservation> GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,OpenLabID,userid,used,date,lesson,confirmed ");
            strSql.Append(" FROM OpenReservation ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            SqlDataReader dr = DbHelperSQL.ExecuteReader(strSql.ToString());
            List<Model.OpenReservation> list = new List<Model.OpenReservation>();
            if (dr.HasRows)
            {
                Model.OpenReservation m;
                while (dr.Read())
                {
                    m = new Model.OpenReservation();
                    m.id = int.Parse(dr[0].ToString());
                    m.OpenLabID = int.Parse(dr[1].ToString());
                    m.userid = int.Parse(dr[2].ToString());
                    m.used = dr[3].ToString();
                    m.date = Convert.ToDateTime(dr[4]);
                    m.lesson = int.Parse(dr[5].ToString());
                    m.confirmed = Convert.ToBoolean(dr[6]);
                    m.labValue = LabService.SelectLabByID(m.OpenLabID);
                    m.lessonValue = ParameterService.SelectOne(m.lesson);
                    m.userValue = UsersService.SelectByID(m.userid);
                    list.Add(m);
                }
                dr.Close();
            }
            dr.Close();
            return list;
        }

    }
}
